<?php
class omeanalysts_mdl_ome_neworder extends dbeav_model{

    var $has_export_cnf = true;
    var $objMath = null;
    var $export_name = '仓库收到不扣减应收的快递退货表';

    public function table_name($real=false){
        $table_name = '';
        if($real){
            return kernel::database()->prefix.$table_name;
        }else{
            return $table_name;
        }
    }
    public function count($filter=null){
        $where = $this->_filter($filter);
        $sql = "SELECT count(*) as _count FROM sdb_ome_delivery_items_detail AS sodid
                LEFT JOIN sdb_ome_order_items AS sooi ON sodid.order_item_id = sooi.item_id
                LEFT JOIN sdb_ome_delivery_items AS sodi ON sodi.item_id = sodid.delivery_item_id
                LEFT JOIN sdb_ome_delivery AS sod ON sod.delivery_id = sodi.delivery_id
                LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sodid.order_id
                LEFT JOIN sdb_ome_shop AS osh ON soo.shop_id = osh.shop_id
                LEFT JOIN sdb_wms_delivery AS wd ON wd.outer_delivery_bn = sod.delivery_bn
                LEFT JOIN sdb_wms_orderform_delis AS wof ON (wof.delivery_id = wd.delivery_id)
                LEFT JOIN sdb_wms_delivery_bill AS wdb ON (wdb.delivery_id = wd.delivery_id)
                LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sodid.product_id)
                WHERE ".$where." ";
        $row = $this->db->select($sql);
        return intval($row[0]['_count']);
    }

    public function getlist($cols='*', $filter=array(), $offset=0, $limit=-1, $orderType=null){
        if(!$filter['shop_id']){
            $filter['shop_id'] = $this->shopids;
        }else{
            $filter['shop_id'] = $filter['shop_id'];
        }
        $this->objMath = kernel::single('eccommon_math');
        if ($filter['data_type'] == 'pay') {
            $sqlstr = " wd.status=3 AND sooi.`delete` != 'true' ";
            if ($filter['time_from']) {
                $sqlstr .= " AND soo.paytime >= " . strtotime($filter['time_from']) . "  ";
            }
            if ($filter['time_to']) {
                $sqlstr .= " AND soo.paytime < " . strtotime($filter['time_to'] . ' 23:59:59') . "  ";
            }
        }
        if ($filter['data_type'] == 'send') {
            $sqlstr = " wd.status=3 AND sooi.`delete` != 'true' ";
            if ($filter['time_from']) {
                $sqlstr .= " AND wd.delivery_time >= " . strtotime($filter['time_from']) . "  ";
            }
            if ($filter['time_to']) {
                $sqlstr .= " AND wd.delivery_time < " . strtotime($filter['time_to'] . ' 23:59:59') . "  ";
            }
        }
            //货号
            if ($filter['materiaBn']) {
                $materiaBn=$filter['materiaBn'];
                //$sqlstr .= " AND osh.shop_id ='" . $params['shop_id'] . "'  ";
                $sqlstr .= " AND sooi.bn = '$materiaBn'";
            }
            //客户名称
            if ($filter['customername']) {
                $customername=$filter['customername'];
                $sqlstr .= " AND osh.customername ='$customername'";
            }
            if ($filter['shop_id']) {
                $shop_id = implode("','", $filter['shop_id']);
                if(empty($shop_id)){
                    $shop_id = $filter['shop_id'];
                }
                $sqlstr .= " AND osh.shop_id IN ('" . $shop_id . "')  ";
            }
            if ($filter['order_bn']) {
                $order_sn=$filter['order_bn'];
                $sqlstr .= " AND soo.order_bn ='$order_sn'";
            }



            $sql = " SELECT
                soo.`order_id`,
                soo.`order_bn`,
                soo.`ship_area`,
                soo.`ship_addr`,
                soo.`status` AS 'orders_status',
                soo.`pay_status`,
                soo.`paytime`,
                soo.`mark_text` ,
                soo.`total_amount`,
                soo.`cost_item`,
                soo.`cost_freight`,
                soo.`cost_protect`,
                soo.`cost_tax`,
                soo.`discount`,
                soo.`pmt_goods`,
                soo.`pmt_order`,
                soo.`payed`,

                sod.`ship_mobile`,
                sod.`ship_name`,

                sooi.`name` AS 'order_items_name',
                sooi.`bn` AS 'order_items_bn',
                sooi.`amount` AS 'order_items_amount',
                sooi.`cost` AS 'order_items_cost',
                sooi.`price` AS 'order_items_price',
                sooi.`sale_price` AS 'order_items_sale_price',
                sooi.`pmt_price` AS 'order_items_pmt_price',
                sooi.`nums` AS 'order_items_nums' ,
                sooi.`sendnum` AS 'order_items_sendnum' ,
                sooi.`return_num` AS 'order_items_return_num' ,

                sodid.`amount` AS 'delivery_detail_amount' ,
                sodid.`price` AS 'delivery_detail_price' ,
                sodid.`number` AS 'delivery_detail_number' ,
                sodid.order_item_id,

                osh.`name` AS 'shop_name' ,
                wd.`delivery_id` AS 'wms_delivery_id',
                wdb.`logi_no` ,
                wd.`logi_name` ,
                wd.`status` ,
                wd.`delivery_bn` ,
                 wd.`delivery_logi_number` ,
                wd.`delivery_time`,
                wof.`last_modify`,
                smbme.`unit` AS 'unit' , '' AS '退货确认时间',
                sooi.pmt_price/sooi.nums AS order_pmt_price,
                soo.createway

                FROM sdb_ome_delivery_items_detail AS sodid
                LEFT JOIN sdb_ome_order_items AS sooi ON sodid.order_item_id = sooi.item_id
                LEFT JOIN sdb_ome_delivery_items AS sodi ON sodi.item_id = sodid.delivery_item_id
                LEFT JOIN sdb_ome_delivery AS sod ON sod.delivery_id = sodi.delivery_id
                LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sodid.order_id
                LEFT JOIN sdb_ome_shop AS osh ON soo.shop_id = osh.shop_id
                LEFT JOIN sdb_wms_delivery AS wd ON wd.outer_delivery_bn = sod.delivery_bn
                LEFT JOIN sdb_wms_orderform_delis AS wof ON (wof.delivery_id = wd.delivery_id)
                LEFT JOIN sdb_wms_delivery_bill AS wdb ON (wdb.delivery_id = wd.delivery_id)
                LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sodid.product_id)
                WHERE ". $sqlstr . " AND (sod.parent_id =0 or sod.parent_id is NULL)  AND soo.process_status != 'cancel' AND soo.status != 'dead' AND soo.is_fail='false'
                ORDER BY soo.order_id ASC
                 LIMIT " . $offset . "," . $limit;
        $rows = $this->db->select($sql);
        $this->tidy_data($rows, $cols);
        $delivery = app::get('wms')->model('delivery');
        $res = array();
        if ($rows) {
            foreach ($rows as $k => $v) {
                $remark = '';
                if ($v['mark_text']) {
                    $v['mark_text'] = unserialize($v['mark_text']);
                    foreach ($v['mark_text'] as $value) {
                        $remark .= implode(' ', $value);
                    }
                }
                //备注
                $sql = " SELECT GROUP_CONCAT(pmt_describe SEPARATOR ' | ') as remark "
                    . "FROM sdb_ome_order_pmt where order_id = '" . $v['order_id'] . "' group by order_id ";
                $rows = $this->db->select($sql);
                if(!empty($rows[0])) {
                    $remark .= ' 优惠方案：' . $rows[0]['remark'];
                }
                if(false) {  //订单级别优惠平摊到每个SKU上
                    //获取退款金额
                    $refunds_sql = " SELECT IFNULL( SUM(money), 0) AS 'money' FROM sdb_ome_refunds WHERE status ='succ' AND order_id = '" . $v['order_id'] . "'";
                    $refunds_money = $this->db->selectRow($refunds_sql);

                    //按照货品总数量计算均摊
                    $bn_quantity_sql = " SELECT SUM(quantity) AS 'nums' FROM sdb_ome_order_objects WHERE order_id = '" . $v['order_id'] . "' AND `delete` = 'false' "; //AND obj_type = 'goods'
                    $bn_quantity_nums = $this->db->selectrow($bn_quantity_sql);
                    $calc_nums = $bn_quantity_nums;

                    //订单明细货品优惠总金额摊分到该SKU上是多少
                    $order_items_pmt_price_single = $this->objMath->number_div(array($v['order_items_pmt_price'], $v['order_items_nums']));

                    //订单促销优惠平均摊分到每个SKU上是多少
                    $order_items_pmt_bn_single = $this->objMath->number_div(array($v['pmt_order'], $calc_nums['nums']));
                    $order_items_pmt_bn_sum = $this->objMath->number_multiple(array($order_items_pmt_bn_single, $v['order_items_nums']));

                    //订单快递费用平均摊分到每个货品上是多少
                    $order_items_cost_freight_bn_single = $this->objMath->number_div(array($v['cost_freight'], $calc_nums['nums']));
                    $order_items_cost_freight_bn_sum = $this->objMath->number_multiple(array($order_items_cost_freight_bn_single, $v['order_items_nums']));

                    //订单折扣平均摊分到每个货品上是多少
                    $order_items_discount_bn_single = $this->objMath->number_div(array($v['discount'], $calc_nums['nums']));
                    $order_items_discount_bn_sum = $this->objMath->number_multiple(array($order_items_discount_bn_single, $v['order_items_nums']));

                    //订单保价费用摊分到每个货品上是多少
                    $order_items_cost_protect_single = -$this->objMath->number_div(array($v['cost_protect'], $calc_nums['nums']));
                    $order_items_cost_protect_sum = $this->objMath->number_multiple(array($order_items_cost_protect_single, $v['order_items_nums']));

                    //订单保价费用摊分到每个货品上是多少
                    $order_items_cost_tax_single = -$this->objMath->number_div(array($v['cost_tax'], $calc_nums['nums']));
                    $order_items_cost_tax_sum = $this->objMath->number_multiple(array($order_items_cost_tax_single, $v['order_items_nums']));

                    //发货单价
                    $delivery_detail_pmt_price = $this->objMath->number_minus(array($v['delivery_detail_price'], $order_items_pmt_bn_single));
                    $delivery_detail_pmt_amount = $this->objMath->number_multiple(array($delivery_detail_pmt_price, $v['delivery_detail_number']));

                    //单个货品优惠总金额
                    $order_items_pmt_single = $this->objMath->number_plus(array($order_items_pmt_price_single, $order_items_pmt_bn_single, $order_items_discount_bn_single));

                }
                else
                { //按每个货品对应金额比例摊分订单的优惠

                    $refunds_info = array();
                    if(in_array($v['pay_status'], array('4', '5'))) {
                        //获取退款金额
                        $refunds_sql = " SELECT IFNULL( SUM(money), 0) AS 'money' FROM sdb_ome_refunds WHERE status ='succ' AND  order_id = '" . $v['order_id'] . "'";
                        $refunds_money = $this->db->selectRow($refunds_sql);

                        $refunds_sql = " SELECT `refund_bn`,`money`,`t_sent` FROM sdb_ome_refunds WHERE status ='succ' AND  order_id = '" . $v['order_id'] . "'";
                        $refunds_money_2 = $this->db->select($refunds_sql);
                        foreach ($refunds_money_2 as $key_refunds => $value_refunds) {
                            $refunds_info[] = "退款单号：" . $value_refunds['refund_bn'];
                            $refunds_info[] = "退款金额：" . $value_refunds['money'];
                            $refunds_info[] = "退款时间：" . date('Y-m-d H:i:s', $value_refunds['t_sent']);
                        }
                        $refunds_info_string = implode(',', $refunds_info);
                    }else{
                        $refunds_info_string = '';
                    }

                    //获取货品总数量
                    $bn_quantity_sql = " SELECT SUM(quantity) AS 'nums' FROM sdb_ome_order_objects WHERE order_id = '" . $v['order_id'] . "' AND `delete` = 'false' "; //AND obj_type = 'goods'
                    $bn_quantity_nums = $this->db->selectrow($bn_quantity_sql);
                    $calc_nums = $bn_quantity_nums;

                    //订单明细货品优惠总金额摊分到该SKU上是多少
                    $order_items_pmt_price_single = $this->objMath->number_div(array($v['order_items_pmt_price'], $v['order_items_nums']));

                    //订单快递费用平均摊分到每个货品上是多少
                    $order_items_cost_freight_bn_single = $this->objMath->number_div(array($v['cost_freight'], $calc_nums['nums']));
                    $order_items_cost_freight_bn_sum = $this->objMath->number_multiple(array($order_items_cost_freight_bn_single, $v['delivery_detail_number']));

                    //订单折扣平均摊分到每个货品上是多少
                    $order_items_discount_bn_single = -$this->objMath->number_div(array($v['discount'], $calc_nums['nums']));
                    $order_items_discount_bn_sum = $this->objMath->number_multiple(array($order_items_discount_bn_single, $v['order_items_nums']));

                    //订单保价费用摊分到每个货品上是多少
                    $order_items_cost_protect_single = -$this->objMath->number_div(array($v['cost_protect'], $calc_nums['nums']));
                    $order_items_cost_protect_sum = $this->objMath->number_multiple(array($order_items_cost_protect_single, $v['order_items_nums']));

                    //订单保价费用摊分到每个货品上是多少
                    $order_items_cost_tax_single = -$this->objMath->number_div(array($v['cost_tax'], $calc_nums['nums']));
                    $order_items_cost_tax_sum = $this->objMath->number_multiple(array($order_items_cost_tax_single, $v['order_items_nums']));


                    //订单促销优惠按比例来分摊
                    //销售总价*(订单促销金额+折扣金额)/((订单支付金额+订单促销金额+折扣金额)*货品数量)
                    $pmt_discount = $this->objMath->number_minus(array($v['pmt_order'], $v['discount']));
                    $pmt_discount_payed = $this->objMath->number_plus(array($pmt_discount, $v['payed']));
                    $sale_pmt = $this->objMath->number_multiple(array($pmt_discount, $v['order_items_sale_price'])); //销售总价*(订单促销金额+折扣金额)
                    $pmt_nums = $this->objMath->number_multiple(array($pmt_discount_payed, $v['order_items_nums'])); //(订单支付金额+订单促销金额+折扣金额)*货品数量
                    $order_items_pmt_bn_single = $this->objMath->number_div(array($sale_pmt, $pmt_nums));
                    $order_items_pmt_bn_sum = $this->objMath->number_multiple(array($order_items_pmt_bn_single, $v['order_items_nums']));

                    //发货单价
                    $delivery_detail_pmt_price = $this->objMath->number_minus(array($v['delivery_detail_price'], $order_items_pmt_bn_single));
                    $delivery_detail_pmt_amount = $this->objMath->number_multiple(array($delivery_detail_pmt_price, $v['delivery_detail_number']));

                    //单个货品优惠总金额
                    $order_items_pmt_single = $this->objMath->number_plus(array($order_items_pmt_price_single, $order_items_pmt_bn_single, $order_items_discount_bn_single));
                }

                $axorderno = $axshipno = array(); $create_time ='';
                $deliverynote_sql = "SELECT wdl.`axorderno`,wdl.`axshipno` ,wdn.`create_time` from  sdb_wms_deliverynote_line AS wdl
                LEFT JOIN sdb_wms_deliverynote AS wdn ON (wdl.deliverynote_id = wdn.id)
                WHERE   wdl.delivery_id = ".$v['wms_delivery_id'];
                $deliverynote_rows = $this->db->select($deliverynote_sql);
                foreach ($deliverynote_rows as $key_deliverynote => $value_deliverynote) {
                    $axorderno[] = $value_deliverynote['axorderno'];
                    $axshipno[] = $value_deliverynote['axshipno'];
                    //$create_time = date('Y-m-d H:i:s', $value_deliverynote['create_time']);
                }
                $orderform_sql = "SELECT wd.create_time from  sdb_wms_orderform AS wd
                LEFT JOIN sdb_wms_orderform_delis  wod ON (wod.orderform_id = wd.id)
                WHERE wod.delivery_id = ".$v['wms_delivery_id'];
                $deliverynote_rows = $this->db->select($orderform_sql);
                $create_time = $deliverynote_rows[0]['create_time'];
//                $create_time =  date('Y-m-d H:i:s', $deliverynote_rows[0]['create_time']);

                $axorderno_string = implode(',', $axorderno);
                $axshipno_string = implode(',', $axshipno);
                $create_time_string = $create_time;

                $res[] = array(
                    //【订单总表级别金额】
                    'order_id' => $v['order_id'],                                  //订单ID
                    'ship_mobile' => $v['ship_mobile'],                            //收货人手机号
                    'ship_name' => $v['ship_name'],                                //收货人
                    'delivery_logi_number' => $v['delivery_logi_number'],            //wms已发货数量
                    'order_bn' =>  "\t".$v['order_bn'],                                 //订单编号
                    'ship_addr' => $v['ship_addr'],                                  //地区
                    'ship_area' => $v['ship_area'],                                  //地址
                    'orders_status' => $v['orders_status'],                        //订单状态
                    'pay_status' => $this->get_pay_status($v['pay_status']),       //订单支付状态
                    'paytime' => $v['paytime'], //订单支付时间
//                    'paytime' => $v['paytime'] ? date("Y-m-d H:m:s", $v['paytime']) : '', //订单支付时间
                    //'mark_text' => $v['mark_text'],
                    'total_amount' => $v['total_amount'],                          //订单总额
                    'cost_item'    => $v['cost_item'],                             //订单物料总额
                    'cost_freight' => $v['cost_freight'],                          //配送费用
                    'cost_protect' => $v['cost_protect'],                          //保价费用
                    'cost_tax'     => $v['cost_tax'],                              //税金
                    'discount'     => -$v['discount'],                              //折扣
                    'pmt_goods'    => $v['pmt_goods'],                             //商品促销优惠
                    'pmt_order'    => $v['pmt_order'],                             //订单促销优惠
                    'payed'        => $v['payed'],                                 //已支付金额
                    'refunds_money'  => $refunds_money['money'],                                 //退款金额

                    //【订单明细表级别金额】
                    'order_items_name' => $v['order_items_name'],                  //宝贝名称
                    'order_items_bn'   => $v['order_items_bn'],                    //货号
                    'order_items_amount'       => $v['order_items_amount'],        //订单明细货品总金额 货品小计 = 单价 x 数量
                    'order_items_cost'         => $v['order_items_cost'],          //订单明细货品成本价
                    'order_items_price'        => $v['order_items_price'],         //订单明细货品购买单价
                    'order_items_sale_price'   => $v['order_items_sale_price'],    //订单明细货品销售总价
                    'order_items_pmt_price'    => $v['order_items_pmt_price'],     //订单明细货品优惠总金额
                    'order_items_pmt_price_single'    => $order_items_pmt_price_single,     //订单明细货品优惠摊分到每个SKU金额
                    'order_items_pmt_bn_sum'   => $order_items_pmt_bn_sum,         //订单优惠摊分到每个货品金额
                    'order_items_pmt_bn_sigle' => $order_items_pmt_bn_single,       //订单优惠摊分到每个SKU金额
                    'order_items_cost_freight_bn_sum' => $order_items_cost_freight_bn_sum,       //配送费用摊分到每个货品金额
                    'order_items_cost_freight_bn_sigle' => $order_items_cost_freight_bn_single,   //配送费用摊分到每个SKU金额
                    'order_items_discount_bn_sum' => $order_items_discount_bn_sum,           //折扣费用摊分到每个货品金额
                    'order_items_discount_bn_sigle' => $order_items_discount_bn_single,       //折扣费用摊分到每个SKU金额
                    'order_items_cost_protect_sum' => $order_items_cost_protect_sum,       //保价费用摊分到每个SKU金额
                    'order_items_cost_protect_single' => $order_items_cost_protect_single,       //保价费用摊分到每个SKU金额
                    'order_items_cost_tax_sum' => $order_items_cost_tax_sum,       //税金费用摊分到每个SKU金额
                    'order_items_cost_tax_single' => $order_items_cost_tax_single,       //税金费用摊分到每个SKU金额
                    'order_items_pmt_single' => $order_items_pmt_single, //订单明细货品优惠总金额

                    'order_items_nums'         => $v['order_items_nums'],          //订单明细货品总数量
                    'order_items_sendnum'      => $v['order_items_sendnum'],       //订单明细货品已发货数量
                    'order_items_return_num'   => $v['order_items_return_num'],    //订单明细货品已退货数量

                    //【发货明细详情表级别金额】
                    'delivery_detail_amount'   => $v['delivery_detail_amount'],    //商品优惠后发货明细货品销售总金额
                    'delivery_detail_price'   => $v['delivery_detail_price'],      //发货明细货品销售单价
                    'delivery_detail_pmt_amount'   => $delivery_detail_pmt_amount,      //发货优惠后销售总金额
                    'delivery_detail_pmt_price'   => $delivery_detail_pmt_price, //发货明细货品优惠后的金额
                    'delivery_detail_cost_freight' => $this->objMath->number_multiple(
                        array(
                            $order_items_cost_freight_bn_single,
                            $v['delivery_detail_number']
                        )
                    ), //发货明细配送费用金额
                    'delivery_detail_number'   => $v['delivery_detail_number'],    //发货明细货品总数量
                    'shop_name' => $v['shop_name'],                                //店铺名称
                    'logi_no' => $v['logi_no'],                                    //快递单号
                    'logi_name' => $v['logi_name'],                                //快递单号
                    'status' => $delivery->get_process_status($v['status']),       //进度状态
                    'delivery_bn' => $v['delivery_bn'],                            //发货单号
                    'axorderno' => $axorderno_string,                                //AX销售订单号
                    'axshipno' => $axshipno_string,                                  //AX送货单号
                    'create_time' => $create_time_string,//备货时间
                    'ax_time' => $v['delivery_time'] ,//订单发货时间
//                    'ax_time' => $v['delivery_time'] ? date("Y-m-d H:m:s", $v['delivery_time']) : '',//订单发货时间
                    'unit' => $v['unit'],

                    'order_item_number' => $v['order_item_number'],                //订单明细货品总数量
                    'number' => $v['delivery_detail_number'],                      //订单数量
                    'axnumber' => $v['delivery_detail_number'],                    //备货数量
                    'd_number' => $v['
                    '],                    //发货数量
                    'd_price' => $v['price'],                                      //发货单价
                    'd_sale_price' => $v['sale_price'],                            //发货金额

                    't_ready' => $refunds_info_string,
                    't_count' => $v['back_number'],                            //退款数量
                    't_money' => $refunds_money['money'],                          //退款金额
                    'remark' => $remark ? '订单备注：' . $remark : '',              //订单备注
                    'order_pmt_price'=>$v['order_pmt_price'],
                    'createway'=>$v['createway'],
                    
                );

                unset($axorderno_string);
                unset($axshipno_string);
                unset($create_time_string);
                unset($delivery_detail_pmt_amount);
                unset($delivery_detail_pmt_price);
                unset($order_items_pmt_price_single);
                unset($order_items_pmt_bn_sum);
                unset($order_items_pmt_bn_single);
                unset($order_items_cost_freight_bn_sum);
                unset($order_items_cost_freight_bn_single);
                unset($order_items_discount_bn_sum);
                unset($order_items_discount_bn_single);
                unset($order_items_pmt_single);
                unset($refunds_info);
                unset($refunds_info_string);
                unset($refunds_money);
                unset($remark);
            }
        }
        return ($res);
    }


    function get_pay_status($pay_status_key){
        $pay_status = array(
            '0' => '未支付',
            '1' => '已支付',
            '2' => '处理中',
            '3' => '部分付款',
            '4' => '部分退款',
            '5' => '全额退款',
            '6' => '退款申请中',
            '7' => '退款中',
            '8' => '支付中'
        );

        return $pay_status[$pay_status_key];
    }

    public function searchOptions(){
        $parentOptions = parent::searchOptions();
        // $childOptions = array(
        //     'order_bn'=>app::get('base')->_('订单号'),
        // );
        return $Options = array_merge($parentOptions,$childOptions);
    }

    /**从控制器获取  当前管理员的shopid
     * @param $shopids
     */
    public function setShopids($shopids){
        $this->shopids=$shopids;

    }
    public function _filter($filter,$tableAlias=null,$baseWhere=null){
        if(!$filter['shop_id']){
            $filter['shop_id'] = $this->shopids;
        }else{
            $filter['shop_id'] = $filter['shop_id'];
        }
        $where=array(1);
        if ($filter['data_type'] == 'send') {
            $where[]= " wd.status=3 AND sooi.`delete` != 'true' ";
            if ($filter['time_from']) {
                $where[]= " wd.delivery_time >= " . strtotime($filter['time_from']) . "  ";
            }
            if ($filter['time_to']) {
                $where[]= " wd.delivery_time < " . strtotime($filter['time_to'] . ' 23:59:59') . "  ";
            }
        }

        if ($filter['data_type'] == 'pay') {
            $where[]= " wd.status=3 AND sooi.`delete` != 'true' ";
            if ($filter['time_from']) {
                $where[]= " soo.paytime >= " . strtotime($filter['time_from']) . "  ";
                unset($filter['time_from']);
            }
            if ($filter['time_to']) {
                $where[]= " soo.paytime < " . strtotime($filter['time_to'] . ' 23:59:59') . "  ";
                unset($filter['time_to']);
            }
            unset($filter['data_type']);
        }

        //货号
        if ($filter['materiaBn']) {
            $materiaBn=$filter['materiaBn'];
            //$sqlstr .= " AND osh.shop_id ='" . $params['shop_id'] . "'  ";
            $where[]= "  sooi.bn = '$materiaBn'";
            unset($filter['materiaBn']);
        }
        //客户名称
        if ($filter['customername']) {
            $customername=$filter['customername'];
            $where[]= " osh.customername ='$customername'";
        }
        if ($filter['shop_id']) {
//            $shop_id = $filter['shop_id'];
            $shop_id = implode("','", $filter['shop_id']);
            if(empty($shop_id)){
                $shop_id = $filter['shop_id'];
            }
            $where[]= " osh.shop_id IN ('" . $shop_id . "')  ";
            unset($filter['shop_id']);
        }
        if ($filter['order_bn']) {
            $order_sn=$filter['order_bn'];
            $where[]= " soo.order_bn ='$order_sn'";
        }
        return implode($where,' AND ');

    }

    public function get_schema(){
        $schema = array (
            'columns' => array (
                'shop_name' => array (
                    'type' => 'varchar(255)',
                    'label' => '店铺名称',
                    'comment' => '店铺名称',
                    'width' => 150,
                    'editable' => false,
                ),
                'order_bn' => array (
                    'type' => 'varchar(32)',
                    'required' => true,
                    'label' => '订单号',
                    'comment' => '订单号',
                    'editable' => false,
                    'width' =>150,
                    'searchtype' => 'has',
                    'filtertype' => 'yes',
                    'filterdefault' => true,
                    'is_title' => true,
                ),
                 'order_items_bn' => array (
                     'type' => 'varchar(40)',
                     'label' => '货号',
                     'comment' => '货号',
                     'editable' => false,
                     'width' =>100,

                 ),
                'order_items_name' => array (
                    'type' => 'varchar(200)',
                    'label' => '宝贝名称',
                    'comment' => '宝贝名称',
                    'editable' => false,
                    'width' =>130,
                ),
                'status' => array (
                    'type' => 'tinyint(1)',
                    'required' => true,
                    'label' => '进度状态',
                    'comment' => '进度状态',
                    'editable' => false,
                    'width' =>50,

                ),
                'unit' => array (
                    'type' => 'varchar(20)',
                    'required' => true,
                    'label' => '单位',
                    'comment' => '单位',
                    'editable' => false,
                    'width' =>50,

                ),
                'ship_name' => array (
                    'type' => 'varchar(50)',
                    'label' => '收货人',
                    'comment' => '收货人姓名',
                    'editable' => false,
                    'width' =>75,

                ),
                'ship_mobile' => array (
                    'type' => 'varchar(50)',
                    'label' => '收货人手机',
                    'comment' => '收货人手机',
                    'editable' => false,
                    'width' =>75,

                ),
                'order_items_nums' => array (
                    'type' => 'mediumint(8)',
                    'comment' => '订单数量',
                    'editable' => false,
                    'label' => '订单数量',
                    'width' =>50,
                ),
                'delivery_logi_number' => array (
                    'type' => 'mediumint(8)',
                    'comment' => '已发货数量',
                    'editable' => false,
                    'label' => '已发货数量',
                    'width' =>50,
                ),
                'order_items_return_num' => array (
                    'type' => 'mediumint(8)',
                    'comment' => '已退货数量',
                    'editable' => false,
                    'label' => '已退货数量',
                    'width' =>50,
                ),
                'delivery_detail_number' => array (
                    'type' => 'varchar(100)',
                    'label' => '发货数量',
                    'comment' => '发货数量',
                    'editable' => false,
                    'width' =>75,
                ),
                'paytime' => array (
                    'type' => 'time',
                    'label' => '下单时间',
                    'comment' => '下单时间',
                    'editable' => false,
                    'width' =>125,
                ),
                'ax_time' => array (
                    'type' => 'time',
                    'label' => '订单发货时间',
                    'comment' => '订单发货时间',
                    'width' =>125,
                    'editable' => false,
                ),
                'delivery_detail_number' => array (
                    'type' => 'mediumint(8)',
                    'editable' => false,
                    'label' => '备货数量',
                    'comment' => '备货数量',
                    'width' =>75,

                ),
                'create_time' => array (
                    'type' => 'time',
                    'editable' => false,
                    'label' => '备货时间',
                    'comment' => '备货时间',
                    'width' =>125,

                ),

                'pay_status' => array (
                    'type' => "enum('0','1','2','3','4','5','6','7','8')",
                    'editable' => false,
                    'label' => '订单支付状态',
                    'comment' => '订单支付状态',
                    'width' =>95,

                ),
                'total_amount' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '订单销售价',
                    'comment' => '订单销售价',
                    'width' =>75,

                ),
                'cost_item' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '订单原价',
                    'comment' => '订单原价',
                    'width' =>75,

                ),
                'cost_freight' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '配送费用',
                    'comment' => '配送费用',
                    'width' =>75,

                ),
                'cost_protect' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '保价费用',
                    'comment' => '保价费用',
                    'width' =>75,

                ),
                'cost_tax' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '税金',
                    'comment' => '税金',
                    'width' =>50,

                ),
                'discount' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '折扣',
                    'comment' => '折扣',
                    'width' =>50,

                ),
                'pmt_goods' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '商品促销优惠',
                    'comment' => '商品促销优惠',
                    'width' =>105,

                ),
                'pmt_order' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '整笔订单促销优惠',
                    'comment' => '整笔订单促销优惠',
                    'width' =>85,

                ),
                'payed' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '优惠后订单金额',
                    'comment' => '优惠后订单金额',
                    'width' =>85,

                ),
                'refunds_money' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '订单已退款金额',
                    'comment' => '订单已退款金额',
                    'width' =>85,

                ),
                'order_items_pmt_price_single' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '商品优惠分摊',
                    'comment' => '商品优惠分摊',
                    'width' =>105,

                ),
                'order_items_pmt_bn_sigle' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '整笔订单优惠分摊',
                    'comment' => '整笔订单优惠分摊',
                    'width' =>85,

                ),
                'order_items_discount_bn_sigle' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '折扣费用分摊',
                    'comment' => '折扣费用分摊',
                    'width' =>105,

                ),
                'order_items_cost_freight_bn_sigle' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '配送费用分摊',
                    'comment' => '配送费用分摊',
                    'width' =>105,

                ),
                'order_items_cost_protect_single' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '保价费用分摊',
                    'comment' => '保价费用分摊',
                    'width' =>105,

                ),
                'order_items_cost_tax_single' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '税金分摊',
                    'comment' => '税金分摊',
                    'width' =>75,

                ),
                'order_items_pmt_single' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '分摊汇总',
                    'comment' => '分摊汇总',
                    'width' =>75,

                ),
                'delivery_detail_pmt_amount' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '优惠后发货金额',
                    'comment' => '优惠后发货金额',
                    'width' =>85,

                ),
                'order_items_cost_freight_bn_sigle' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '优惠后发货单价',
                    'comment' => '优惠后发货单价',
                    'width' =>85,

                ),
                'order_items_price' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '货品原价',
                    'comment' => '货品原价',
                    'width' =>75,

                ),
                'delivery_detail_cost_freight' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '发货配送费用',
                    'comment' => '发货配送费用',
                    'width' =>105,

                ),
                'axorderno' => array (
                    'type' => 'varchar(50)',
                    'editable' => false,
                    'label' => 'AX销售订单号',
                    'comment' => 'AX销售订单号',
                    'width' =>85,

                ),
                'axshipno' => array (
                    'type' => 'varchar(50)',
                    'editable' => false,
                    'label' => 'AX送货单号',
                    'comment' => 'AX送货单号',
                    'width' =>120,

                ),
                'logi_no' => array (
                    'type' => 'varchar(50)',
                    'editable' => false,
                    'label' => '快递单号',
                    'comment' => '快递单号',
                    'width' =>75,

                ),
                'logi_name' => array (
                    'type' => 'varchar(50)',
                    'editable' => false,
                    'label' => '快递公司',
                    'comment' => '快递公司',
                    'width' =>75,

                ),
                't_ready' => array (
                    'type' => 'time',
                    'editable' => false,
                    'label' => '退款确认时间',
                    'comment' => '退款确认时间',
                    'width' =>105,

                ),
                't_count' => array (
                    'type' => 'varchar(50)',
                    'editable' => false,
                    'label' => '退款数量',
                    'comment' => '退款数量',
                    'width' =>75,

                ),
                't_money' => array (
                    'type' => 'varchar(50)',
                    'editable' => false,
                    'label' => '退款金额',
                    'comment' => '退款金额',
                    'width' =>75,

                ),
                'remark' => array (
                    'type' => 'varchar(50)',
                    'editable' => false,
                    'label' => '备注',
                    'comment' => '备注',
                    'width' =>50,

                ),

                'ship_area' => array (
                    'type' => 'region',
                    'label' => '地区',
                    'comment' => '地区',
                    'editable' => false,
                    'filtertype' => 'normal',
                    'filterdefault' => true,
                    'width' =>120,
                ),
                'ship_addr' => array (
                    'type' => 'varchar(100)',
                    'label' => '地址',
                    'comment' => '地址',
                    'width' => 120,
                    'editable' => false,
                    'filtertype' => 'normal',
                    'in_list' => true,
                    'is_title' => true,
                ),
                'order_pmt_price' => array (
                    'type' => 'money',
                    'editable' => false,
                    'label' => '实付金额',
                    'comment' => '实付金额',
                    'width' =>105,

                ),
                'createway' => array(
                    'type' => array(
                        'matrix' => '平台获取',
                        'local' => '手工新建',
                        'import' => '批量导入',
                        'after' => '售后自建',
                    ),
                    'label' => '订单生成类型',
                    'default' => 'matrix',
                    'required' => true,
                    'in_list' => true,
                    'filtertype' => 'normal',
                ),
            ),
            'idColumn' => 'delivery_bn',
            'in_list' => array (
                0 => 'shop_name',
                1 => 'order_bn',
                2 => 'order_items_bn',
                3 => 'order_items_name',
                4 => 'status',

                5 => 'unit',
                6 => 'order_items_nums',
                7 => 'delivery_detail_number',
                8 => 'paytime',
                9 => 'ax_time',

                10 => 'delivery_detail_number',
                11 => 'create_time',
                12 => 'pay_status',
                13 => 'total_amount',
                14 => 'cost_item',

                15 => 'cost_freight',
                16 => 'cost_protect',
                17 => 'cost_tax',
                18 => 'discount',
                19 => 'pmt_goods',

                20 => 'pmt_order',
                21 => 'payed',
                22 => 'refunds_money',
                23 => 'order_items_pmt_price_single',
                24 => 'order_items_pmt_bn_sigle',

                25 => 'order_items_discount_bn_sigle',
                26 => 'order_items_cost_freight_bn_sigle',
                27 => 'order_items_price',
                28 => 'delivery_detail_cost_freight',
                29 => 'axorderno',

                30 => 'axshipno',
                31 => 'logi_no',
                32 => 'logi_name',
                33 => 't_ready',
                34 => 't_count',

                35 => 't_money',
                36 => 'remark',
                37 => 'ship_area',
                38 => 'ship_addr',

                39 => 'ship_name',
                40 => 'ship_mobile',

                41 => 'delivery_logi_number',
                42 => 'order_items_return_num',
                43 => 'order_pmt_price',
                44 => 'createway',

            ),
            'default_in_list' => array (
                0 => 'shop_name',
                1 => 'order_bn',
                2 => 'order_items_bn',
                3 => 'order_items_name',
                4 => 'status',

                5 => 'unit',
                6 => 'order_items_nums',
                7 => 'delivery_detail_number',
                8 => 'paytime',
                9 => 'ax_time',

                10 => 'delivery_detail_number',
                11 => 'create_time',
                12 => 'pay_status',
                13 => 'total_amount',
                14 => 'cost_item',

                15 => 'cost_freight',
                16 => 'cost_protect',
                17 => 'cost_tax',
                18 => 'discount',
                19 => 'pmt_goods',

                20 => 'pmt_order',
                21 => 'payed',
                22 => 'refunds_money',
                23 => 'order_items_pmt_price_single',
                24 => 'order_items_pmt_bn_sigle',

                25 => 'order_items_discount_bn_sigle',
                26 => 'order_items_cost_freight_bn_sigle',
                27 => 'order_items_price',
                28 => 'delivery_detail_cost_freight',
                29 => 'axorderno',

                30 => 'axshipno',
                31 => 'logi_no',
                32 => 'logi_name',
                33 => 't_ready',
                34 => 't_count',

                35 => 't_money',
                36 => 'remark',
                37 => 'ship_area',
                38 => 'ship_addr',
                39 => 'ship_name',
                40 => 'ship_mobile',

                41 => 'delivery_logi_number',
                42 => 'order_items_return_num',
                43 => 'order_pmt_price',
                44 => 'createway',
            ),
        );
        return $schema;
    }

    /**
     * 获得日志类型(non-PHPdoc)
     * @see dbeav_model::getLogType()
     */
    public function getLogType($logParams) {
        $type = $logParams['type'];
        $logType = 'none';
        if ($type == 'export') {
            $logType = $this->exportLogType($logParams);
        }
        elseif ($type == 'import') {
            $logType = $this->importLogType($logParams);
        }
        return $logType;
    }
    /**
     * 导出日志类型
     * @param Array $logParams 日志参数
     */
    public function exportLogType($logParams) {
        $params = $logParams['params'];
        $type = 'report';
        if ($logParams['app'] == 'omeanalysts' && $logParams['ctl'] == 'ome_analysis') {
            $type .= '_purchaseReport_expressAnalysis';
        }
        $type .= '_export';
        return $type;
    }
    /**
     * 导入操作日志类型
     * @param Array $logParams 日志参数
     */
    public function importLogType($logParams) {
        $params = $logParams['params'];
        $type = 'report';
        if ($logParams['app'] == 'omeanalysts' && $logParams['ctl'] == 'ome_analysis') {
            $type .= '_purchaseReport_expressAnalysis';
        }
        $type .= '_import';
        return $type;
    }

    //根据查询条件获取导出数据
    public function getExportDataByCustom($fields, $filter, $has_detail, $curr_sheet, $start, $end){
//        error_log(var_export($filter,true)."\n\t",3,PUBLIC_DIR."/wherefilter222.log");
////根据选择的字段定义导出的第一行标题
        if($curr_sheet == 1){
            $data['content']['main'][] = $this->getExportTitle($fields);
        }
        if(!$omedelivery = $this->getList('*',$filter,$start,$end)) return $data;
//                error_log(var_export($omedelivery,true)."\n\t",3,PUBLIC_DIR."/data_omedelivery.log");

        $new_omedelivery = array();
        foreach ($omedelivery as $k => $aFilter) {
            foreach (explode(',', $fields) as $key => $col) {
                $new_omedelivery[$k][$col] = $aFilter[$col] ? $aFilter[$col] : '';
//                if(isset($aFilter[$col])){
//                    $new_omedelivery[$k][$col] = $aFilter[$col];
//                }
            }
        }

        $new_rows = $this->formatCsvExport($new_omedelivery);
        foreach ($new_rows as $k => $aFilter) {
            $exptmp_data = array();
            foreach ($aFilter as $col => $val) {
                $aFilter[$col] = mb_convert_encoding($aFilter[$col], 'GBK', 'UTF-8');
                $exptmp_data[] = $aFilter[$col];
            }

            $data['content']['main'][] = implode(',', $exptmp_data);
        }

        return $data;
    }
}
